insert
    overwrite
    table jms_dm.dm_tab_departure_build_package_waybill_detail_dt
    partition
    (dt)
select t1.waybill_no        as waybill_no        --运单
     , t1.package_code      as package_code      --包号
     , t1.scan_site_code    as scan_site_code    --建包中心code
     , t1.scan_site         as scan_site         --建包中心name
     , t1.scan_time         as scan_time         --建包扫描时间
     , t1.site_agent_code   as site_agent_code   --建包中心所属代理区code
     , t1.site_agent_name   as site_agent_name   --建包中心所属代理区name
     , t2.pick_network_code as pick_network_code --取件网点code
     , t2.pick_network_name as pick_network_name --取件网点name
     , t2.pick_finance_code as pick_finance_code --寄件财务中心code
     , t1.date_time         as date_time         --扫描日期
     , t1.date_time         as dt
from (
    select waybill_no
         , package_code
         , scan_site_code
         , scan_site
         , scan_time
         , site_agent_code
         , site_agent_name
         , to_date(scan_time)                                                                                     as date_time
         , row_number() over (partition by waybill_no,scan_site_code,to_date(scan_time) order by scan_time desc ) as rn
    from jms_dwd.dwd_tab_barscan_bagging_base_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -0)
      and dt <= '{{ execution_date | cst_ds }}'
      and substr(scan_time, 1, 10) >= date_add('2023-12-22', -0)
      and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
      and substr(package_code, 1, 2) = 'B1' --出港建包
      and network_type = 2                  --中心建包
      and site_agent_code = '420000'        --湖北代理区
      and waybill_no is not null
) t1
         left join
(
    select waybill_no
         , pick_network_code
         , pick_network_name
         , pick_finance_code
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -45)
--           dt >= date_add('{{ execution_date | cst_ds }}', -15)
      and dt <= '{{ execution_date | cst_ds }}'
      and pick_finance_code = '420000'
      and pick_network_code is not null
) t2
on t1.waybill_no = t2.waybill_no
    distribute by dt
   , pmod(hash(waybill_no), 2)
;
